package com.xqgl.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.xqgl.bean.Park;
import com.xqgl.dao.ParkDao;
import com.xqgl.util.ConnertionUtil;

public class ParkDaoImp implements ParkDao{
	Connection conn = ConnertionUtil.getConnection();
	PreparedStatement ps;
	Statement statement;
	
	public ArrayList<Park> getAllUserInfos() {
		ArrayList<Park> parks = new ArrayList<>();
		String sql = "select * from park ";
		try {
			ps = conn.prepareStatement(sql);
			ResultSet rst=ps.executeQuery(sql);
			rst.last();
			rst.beforeFirst();
			while(rst.next()) {
				Park park = new Park();
				park.setId(rst.getInt("id"));
				park.setVillagename(rst.getString("villagename"));
				park.setParknumber(rst.getString("parknumber"));
				park.setParkname(rst.getString("parkname"));
				park.setParkctime(rst.getString("parkctime"));
				park.setParkstate(rst.getString("parkstate"));
				parks.add(park);
			}		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return parks;
	}

	@Override
	public int add(Park park) throws SQLException {
		String sql = "insert into park(villagename,parknumber,parkname,parkctime,parkstate)"
				+ "values(?,?,?,?,?)";
		ps = conn.prepareStatement(sql);
		ps.setString(1, park.getVillagename());
		ps.setString(2, park.getParknumber());
		ps.setString(3, park.getParkname());
		ps.setString(4, park.getParkctime());
		ps.setString(5, park.getParkstate());
		int a = ps.executeUpdate();
		ps.close();
		return a;
	}

	@Override
	public int update(Park park) throws SQLException {
		String sql = "update park set villagename = '"+park.getVillagename()+"', parknumber = '"+park.getParknumber()+"', parkname = '"+park.getParkname()+"', parkctime = '"+park.getParkctime()+"', parkstate = '"+park.getParkstate()+"' where id = "+park.getId()+"";
		ps = conn.prepareStatement(sql);
		int b = ps.executeUpdate();
		ps.close();
		return b;
	}

	@Override
	public boolean delete(int id) throws SQLException {
		String sql = "delete from park where id = "+id+"";
		statement = conn.createStatement();
		boolean c = statement.execute(sql);
		statement.close();
		return !c;
	}

	@Override
	public Park findById(int id) throws SQLException {
		Park park = new Park();
		String sql = "select * from park where id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ResultSet set = ps.executeQuery();
			while(set.next()){
				park.setId(set.getInt("id"));
				park.setVillagename(set.getString("villagename"));
				park.setParknumber(set.getString("parknumber"));
				park.setParkname(set.getString("parkname"));
				park.setParkctime(set.getString("parkctime"));
				park.setParkstate(set.getString("parkstate"));
			}
			ps.close();
			return park;
	}
}
